/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Controller;

import ModelKelas.Pendaftaran;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

/**
 *
 * @author valentinus
 */
public class PendaftaranControl {
public Connection conn;

    public PendaftaranControl(Connection Conn) {
        this.conn = conn;
    }

    public void TambahSatuanObat(Pendaftaran Obat) throws SQLException {

        PreparedStatement statement = null;
        try {
            conn.setAutoCommit(false);
//            String sql = "insert into barang (namaBarang, kodeBarang, kategoriBarang, QtyMax,"
//                    + " QtyMin, ritelSetelah, beliSetelah) values(?,?,?,?,?,?,?)";
//            statement = conn.prepareStatement(sql);
            statement.setString(1, Obat.getAlamatPasien());
            statement.setString(2, Obat.getNamaPasien());
            statement.setString(3, Obat.getNomorPendaftaran());
            statement.setString(4, Obat.getTanggal());
            statement.executeUpdate();
            conn.commit();
        } catch (SQLException ex) {
            try {
                conn.rollback();
            } catch (SQLException ex1) {
                Logger.getLogger(ApotekControl.class.getName()).log(Level.SEVERE, null, ex1);
            }
        }
    }

    public String tambahKodeSatuan(String baru) throws SQLException {
        PreparedStatement statement = null;
        int data = 0;
        ResultSet resulset = null;
        conn.setAutoCommit(false);
//        String sql = "select max (b.kodeBarang)"
//                + " from barang b, kategori k"
//                + " where b.kategoriBarang = ?";
//        statement = conn.prepareStatement(sql);
        statement.setString(1, baru);
        resulset = statement.executeQuery();
        if (resulset.next()) {
            data = Integer.parseInt(resulset.getString(1)) + 1;
            System.out.println(data);
        }
        conn.commit();
        return String.valueOf(data);
    }

    public void CekKarakter(String Obat) {
        String input = "[0-9]";
        String koreksi;
        String chek = Obat;
        if (!chek.matches(input)) {
            JOptionPane.showMessageDialog(null, "data tidak boleh karakter");
        }
    }

//    public void TambahKategoriCall(Kategori kategori) {
//        CallableStatement callset = null;
//        String namanya = "";
//        try {
//            conn.setAutoCommit(false);
//            String sql = "{CALL TAMBAHKATEGORI(?,?)}";
//            callset = conn.prepareCall(sql);
//            callset.setString(1, kategori.getKodeKategori());
//            callset.setString(2, kategori.getNamaKategori());
//            callset.executeQuery();
//        } catch (SQLException ex) {
//            Logger.getLogger(ControlerKategori.class.getName()).log(Level.SEVERE, null, ex);
//        }
//
//    }
//    public String CariKategori2(String kodeKategori) throws SQLException {
//
//        PreparedStatement statement = null;
//        ResultSet result = null;
//        String namaKategori = "";
//        try {
//            conn.setAutoCommit(false);
//            String sql = "select max(kodeBarang) from barang where kategoriBarang=?";
//            statement = conn.prepareStatement(sql);
//            statement.setString(1, kodeKategori);
//            result = statement.executeQuery();
//            while (result.next()) {
////                namaKategori = result.getNString(1
//                namaKategori = result.getString("max(kodeBarang)");
//            }
//            conn.commit();
//        } catch (SQLException ex) {
//
//            Logger.getLogger(ControlerKategori.class.getName()).log(Level.SEVERE, null, ex);
//        }
//        return namaKategori;
//
//    }
//    public String KodeOtomatis(String kodeKategori) throws SQLException {
//        PreparedStatement statement = null;
//        String cek = "";
//        ResultSet result = null;
//        conn.setAutoCommit(false);
//        String sql = "SELECT max(kodeBarang) from barang where kategoriBarang = ?";
//        statement = conn.prepareStatement(sql);
//        statement.setString(1, kodeKategori);
//        result = statement.executeQuery();
//        while (result.next()) {
//            cek = result.getString("max(kodeBarang)");
//        }
//        return cek;
//    }

    public String TambahKategoriCall(Pendaftaran Obat) throws SQLException {

        String nama = Obat.getAlamatPasien();
        String kode = Obat.getNamaPasien();
        String kategori = Obat.getNomorPendaftaran();
        String satuan = Obat.getTanggal();


        CallableStatement callset = null;
        String namanya = "";

        conn.setAutoCommit(false);
        String sql = "insert into barang (namaBarang, kodeBarang, kategoriBarang, Satuan, QtyMax, QtyMin, ritelSetelah, ritelSebelum, beliSetelah, beliSebelum) "
                + "values ('" + nama + "' , '" + kode + "' , '" + kategori + "' , '" + satuan + "')";

        Statement statement = conn.createStatement();
        statement.executeUpdate(sql);

        conn.commit();
        conn.close();

        return namanya;
    }

    public List<Pendaftaran> LihatSeluruhBarang() throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

        conn.setAutoCommit(false);
        String sql = "select namaBarang, kodeBarang, kategoriBarang, Satuan, QtyMax, QtyMin, "
                + "ritelSetelah, ritelSebelum, beliSetelah, belisebelum "
                + "from barang order by kodeBarang";
        statement = conn.prepareStatement(sql);
        result = statement.executeQuery();
        List<Pendaftaran> barang = new ArrayList<Pendaftaran>();
        while (result.next()) {
            Pendaftaran brg = new Pendaftaran();
            brg.setAlamatPasien(result.getString(1));
            brg.setNamaPasien(result.getString(2));
            brg.setNomorPendaftaran(result.getString(3));
            brg.setTanggal(result.getString(4));
            barang.add(brg);
        }
        conn.commit();
        return barang;
    }

    public List<Pendaftaran> LihatSeluruhBarang1() throws SQLException {
        PreparedStatement statement1 = null;
        ResultSet result1 = null;

        conn.setAutoCommit(false);
        String sql = "select b.namaBarang, b.kodeBarang, k.namaKategori, "
                + "b.beliSetelah"
                + " from barang b, kategori k"
                + " where b.kategoriBarang = k.kodeKategori";
        statement1 = conn.prepareStatement(sql);
        result1 = statement1.executeQuery();
        List<Pendaftaran> barang = new ArrayList<Pendaftaran>();
        while (result1.next()) {
            Pendaftaran brg = new Pendaftaran();
            brg.setAlamatPasien(result1.getString(1));
            brg.setNamaPasien(result1.getString(2));
            brg.setNomorPendaftaran(result1.getString(3));
            brg.setTanggal(result1.getString(4));
            barang.add(brg);
        }
        conn.commit();
        return barang;
    }

    public List<Pendaftaran> getSearch(String search) throws SQLException {
        PreparedStatement statementa = null;
        ResultSet result2 = null;

        conn.setAutoCommit(false);
        String sql = "select b.namaBarang, b.kodeBarang, k.namaKategori, b.beliSetelah "
                + "from barang b, kategori k "
                + "where (k.kodeKategori = b.kategoriBarang) and ((b.namaBarang) like '%" + search + "%' or "
                + "b.kodeBarang like '%" + search + "%' or "
                + "(k.namaKategori) like '%" + search + "%' or "
                + "b.beliSetelah like '%" + search + "%')";
        statementa = conn.prepareStatement(sql);
        result2 = statementa.executeQuery();
        List<Pendaftaran> barang1 = new ArrayList<Pendaftaran>();
        while (result2.next()) {
            Pendaftaran brg = new Pendaftaran();
            brg.setNamaPasien(result2.getString(1));
            brg.setAlamatPasien(result2.getString(2));
            brg.setNomorPendaftaran(result2.getString(3));
            brg.setTanggal(result2.getString(4));
            barang1.add(brg);
        }
        conn.commit();
        return barang1;
    }

    public List<Pendaftaran> getSearchKode(String kode1, String kode2) throws SQLException {
        PreparedStatement statement1a = null;
        ResultSet result21 = null;

        conn.setAutoCommit(false);
        statement1a = conn.prepareStatement("select b.namaBarang, b.kodeBarang, k.namaKategori, b.beliSetelah "
                + "from barang b, kategori k "
                + "where b.kategoriBarang = k.kodeKategori and b.kodeBarang between '" + kode1 + "' and '" + kode2 + "' order by b.kodeBarang");
//        statementa.setString(1, SearchKode);
        result21 = statement1a.executeQuery();
        List<Pendaftaran> barangs = new ArrayList<Pendaftaran>();
//        Barang barang = null;

        while (result21.next()) {
            Pendaftaran barang = new Pendaftaran();
//            Kategori kategori = new Kategori();
            barang.setAlamatPasien(result21.getString(1));
            barang.setNamaPasien(result21.getString(2));
            barang.setNomorPendaftaran(result21.getString(3));
            barang.setTanggal(result21.getString(4));
//            barang.setNamaBarang(result2.g1etString("namaBarang"));
//            barang.setKodeBarang(result2.getString("kodeBarang"));
//            kategori.setNamaKategori(result2.getString("namaKategori"));
//            barang.setBeliSetelahPPN(result2.getDouble("beliSetelah"));
//                barang.setKategori(kategori);
            barangs.add(barang);
        }
        conn.commit();
        return barangs;
    }

    public String getSearchNama(String nama1) throws SQLException {
        PreparedStatement statement = null;
//        Barang barang = new Barang();
        String data = "";
        ResultSet resulset = null;
        conn.setAutoCommit(false);
        String sql = "select namaBarang from barang where kodeBarang = '" + nama1 + "'";
        statement = conn.prepareStatement(sql);
//        statement.setString(1, nama1);
//        barang.setNamaBarang(resulset.getString(1));
        resulset = statement.executeQuery();
        if (resulset.next()) {
            data = resulset.getString(1);
            System.out.println(data);
        }
        conn.commit();
        return String.valueOf(data);
//        PreparedStatement statement1a = null;
//        ResultSet result21 = null;

//        conn.setAutoCommit(false);
//        statement1a = conn.prepareStatement("select namaBarang from barang where kodeBarang = '" + nama1 + "'");
////        statementa.setString(1, SearchKode);
//        result21= statement1a.executeQuery();
//        List<Barang> barangs = new ArrayList<Barang>();
////        Barang barang = null;
//
//        while (result21.next()) {
//            Barang barang = new Barang();
////            Kategori kategori = new Kategori();
//            barang.setNamaBarang(result21.getString(1));
////            barang.setKodeBarang(result21.getString(2));
////            barang.setKategoriBarang(result21.getString(3));
////            barang.setBeliSetelahPPN(result21.getDouble(4));
////            barang.setNamaBarang(result2.g1etString("namaBarang"));
////            barang.setKodeBarang(result2.getString("kodeBarang"));
////            kategori.setNamaKategori(result2.getString("namaKategori"));
////            barang.setBeliSetelahPPN(result2.getDouble("beliSetelah"));
////                barang.setKategori(kategori);
//            barangs.add(barang);
//        }
//        conn.commit();
//        return nama1;
    }

    public void UpdateBarang(Pendaftaran brg) throws SQLException {
        PreparedStatement statement = null;
        try {
            conn.setAutoCommit(false);
            String sql = "update barang set namaBarang = ?, kategoriBarang = ?, Satuan = ?, QtyMax = ?, QtyMin = ?, "
                    + "ritelSetelah = ?, ritelSebelum = ?, "
                    + "beliSetelah = ?, beliSebelum = ? "
                    + "where kodeBarang = ?";

            statement = conn.prepareStatement(sql);
            statement.setString(1, brg.getAlamatPasien());
            statement.setString(2, brg.getNamaPasien());
            statement.setString(3, brg.getNomorPendaftaran());
            statement.setString(4, brg.getTanggal());
            statement.executeUpdate();
            conn.commit();
        } catch (SQLException ex) {
            conn.rollback();
            throw ex;
        } finally {
            try {
                conn.setAutoCommit(true);
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException ex) {
                throw ex;
            }
        }
    }

    public void hapusBarang(Pendaftaran brg) throws SQLException {
        PreparedStatement statement = null;
        try {
            conn.setAutoCommit(false);
            String sql = "delete from barang where namaBarang = ?";
//                    + "kodeBarang = ?, "
//                    + "kodeKategori = ?, Satuan = ?, QtyMax = ?, QtyMin = ?, "
//                    + "ritelSetelah = ?, ritelSebelum = ?, "
//                    + "beliSetelah = ?, beliSebelum = ? ";
            statement = conn.prepareStatement(sql);
            statement.setString(1, brg.getNamaPasien());
//            statement.setString(2, brg.getKodeBarang());
//            statement.setString(3, brg.getKategoriBarang());
//            statement.setString(4, brg.getSatuanBarang());
//            statement.setInt(5, brg.getQtyMax());
//            statement.setInt(6, brg.getQtyMin());
//            statement.setDouble(7, brg.getBeliSetelahPPN());
//            statement.setDouble(8, brg.getRitelSebelumPPN());
//            statement.setDouble(9, brg.getBeliSetelahPPN());
//            statement.setDouble(10, brg.getBeliSebelumPPN());

            statement.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        } finally {
            try {
                conn.setAutoCommit(true);
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException ex) {
                throw ex;
            }
        }
    }
}
